﻿--Aller chercher le NodeId en fonction de certains critères

--SELECT n.*, v.*
--  FROM [devMedianamik2].[dbo].[medianamik_Node] n
--inner join medianamik_type t on t.typeid = n.typeid
--inner join medianamik_NodepropertyValue v on v.nodeid = n.nodeid
--where t.name='medianamikpage'
--and v.value = 'userpreferences.aspx'
--order by n.createdon desc


-- NodeId a insérer
DECLARE @NodeId UNIQUEIDENTIFIER
SELECT @NodeId = 'B2F387D8-A03C-44C2-80B6-5F11C06A9716'

-- In sertion dans la table Node
select 
'IF NOT EXISTS( SELECT NULL  FROM medianamik_Node where NodeId = ''' + CAST(NodeID as NVARCHAR(38)) + ''')
BEGIN
INSERT INTO [dbo].[medianamik_Node]
           ([NodeId]
           ,[ParentNodeId]
           ,[TypeId]
           ,[CreatedOn]
           ,[CreatedBy]
           ,[ModifiedOn]
           ,[ModifiedBy]
           ,[Flags]
           ,[IsDeleted]
           ,[Path]
           ,[Depth]
           ,[Position]
           ,[EditedOn]
           ,[EditedBy])
     VALUES
           ('''+CAST(NodeId as nvarchar(38))+'''
           ,'+ISNULL(''''+CAST(ParentNodeId as NVARCHAR(38))+'''', 'NULL')+'           
           ,'''+CAST(TypeId as NVARCHAR(38))+'''
           ,'''+CONVERT(nvarchar(max), CreatedOn,21 ) +'''
           ,'''+CreatedBy+'''
           ,'+ISNULL(''''+CONVERT(nvarchar(max), ModifiedOn, 21)+'''', 'null')+'
           ,'+ISNULL(''''+ModifiedBy+'''', 'NULL')+'
           ,'+STR(Flags)+'
           ,'+STR(IsDeleted)+'
           ,'''+[Path]+'''
           ,'+STR(Depth)+'
           ,'+STR(Position)+'
           ,'+ISNULL(''''+CONVERT(nvarchar(max), EditedOn, 21)+'''', 'null')+'
           ,'''+isnull(EditedBy,'null')+'''
           )
END'

 from medianamik_Node where nodeid =@NodeId

--Insertion dans la table NodeInstance

select 'IF NOT EXISTS(SELECT NULL FROM Medianamik_NodeInstance WHERE NodeId = '''+CAST(NodeId as NVARCHAR(38))+''' AND CultureNAme = '''+culturename+''')
BEGIN
INSERT INTO [dbo].[medianamik_NodeInstance]
           ([NodeInstanceId]
           ,[NodeId]
           ,[IsActive]
           ,[CreatedOn]
           ,[CultureName])
     VALUES
           ('''+cast(NodeInstanceId as nvarchar(38))+'''
           ,'''+cast(NodeId as nvarchar(38))+'''
           ,'+ STR(IsActive) + '
           ,'+ISNULL(''''+CONVERT(nvarchar(max), CreatedOn, 21)+'''', 'null')+'
           ,'''+CultureName+''')
END'

from medianamik_NodeInstance ni 
where nodeid = @NodeId
and culturename <> ''

--Insertion dans la table NodePropertyValue

SELeCT 'IF NOT EXISTS( SELECT NULL FROM medianamik_NodePropertyValue where NodeId = '''+cast(NodeId as nvarchar(38))+''' AND PropertyId = '''+cast(PropertyID as nvarchar(38))+''' AND CultureName = '''+CultureName+''' AND Revision '+ISNULL('='+ STR(Revision), 'IS NULL')+')
BEGIN
INSERT INTO [dbo].[medianamik_NodePropertyValue]
           ([NodeId]
           ,[PropertyId]
           ,[Value]
           ,[Revision]
           ,[CultureName]
           ,[EndRevision]
           )
     VALUES
           ('''+CAST(NodeId as NVARCHAR(38))+'''
           ,'''+CAST(PropertyId as NVARCHAR(38)) + '''
           ,'+ISNULL(''''+REPLACE(Value, '''', '''''' )+'''', 'NULL')+'          
           ,'+ISNULL(STR(Revision), 'null')+'
           ,'''+CultureName+'''
           ,'+isnull(cast(EndRevision as varchar),'null')+'
           )
END'

from Medianamik_NodePropertyValue
where nodeid=@NodeId